<?php
if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class M_layanan extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	function get($kd_ppk='') {
		$sql = 'SELECT kd_layanan, layanan, ppk, tarif, tgl_input, kategori_layanan
		 FROM layanan l  
		 LEFT JOIN master_kategori_layanan mkl ON mkl.id_kategori_layanan=l.id_kategori_layanan
		 LEFT JOIN ppk ON l.kd_ppk=ppk.kd_ppk
		 WHERE l.kd_ppk="'.$kd_ppk.'"
		 ORDER BY kategori_layanan,layanan';
        return $sql;

	}

	function cek1($rule1) {
		$sql = 'SELECT * FROM layanan WHERE kd_layanan=?';
		$query = $this->db->query($sql, $rule1);
        return $query;
	}

	function cek2($rule2) {
		$sql = 'SELECT * FROM layanan WHERE layanan=? AND kd_ppk=?';
		$query = $this->db->query($sql, $rule2);
        return $query;
	}

	function cekUpdate($rule) {
		$sql = 'SELECT * FROM layanan WHERE (kd_layanan=?) AND kd_layanan<>?';
		$query = $this->db->query($sql, $rule);
        return $query;
	}

	function get_cari($keyword='',$kd_ppk='') {
		$sql = 'SELECT kd_layanan, layanan, ppk, tarif, tgl_input, kategori_layanan
		 FROM layanan l  
		 LEFT JOIN master_kategori_layanan mkl ON mkl.id_kategori_layanan=l.id_kategori_layanan
		 LEFT JOIN ppk ON l.kd_ppk=ppk.kd_ppk
		 WHERE (layanan like "%'.$keyword.'%" OR kd_layanan like "%'.$keyword.'%" 
		 OR kategori_layanan like "%'.$keyword.'%" OR tarif like "%'.$keyword.'%")
		 AND l.kd_ppk='.$kd_ppk.'
		 ORDER BY layanan,kategori_layanan';
		return $sql;
	}

	function get_export($params) {
		$sql = 'SELECT kd_layanan, layanan, tarif, tgl_input,perda,ppk,kategori_layanan
		 FROM layanan l 
		 LEFT JOIN master_perda mp ON l.id_perda=mp.id_perda
		 LEFT JOIN ppk ON l.kd_ppk=ppk.kd_ppk
		 LEFT JOIN master_kategori_layanan mkl ON l.id_kategori_layanan=mkl.id_kategori_layanan
		 WHERE l.kd_ppk = ?
		 ORDER BY kategori_layanan,layanan';
		$query = $this->db->query($sql,$params);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_ket($params) {
		$sql = 'SELECT layanan FROM layanan WHERE kd_layanan = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result['layanan'];
	}

	function getPerda(){
		$sql = 'SELECT * FROM master_perda';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function getLayanan(){
		$sql = 'SELECT * FROM master_kategori_layanan';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function getPPK($params) {
		$sql = 'SELECT * FROM ppk
		 WHERE kd_ppk= ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function save($dataInsert) {
		$query = $this->db->insert('layanan', $dataInsert);
		return $query;
	}

	function get_by($params) {
		$sql = 'SELECT kd_layanan, layanan, tarif, tgl_input,perda,ppk,kategori_layanan,
		 l.id_perda AS IDPERDA, l.kd_ppk AS KDPPK, l.id_kategori_layanan AS KAT
		 FROM layanan l 
		 LEFT JOIN master_perda mp ON l.id_perda=mp.id_perda
		 LEFT JOIN ppk ON l.kd_ppk=ppk.kd_ppk
		 LEFT JOIN master_kategori_layanan mkl ON l.id_kategori_layanan=mkl.id_kategori_layanan
		 WHERE l.kd_layanan = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function update($kd_layanan, $dataUpdate) {
		$this->db->where('kd_layanan', $kd_layanan);
		$update = $this->db->update('layanan', $dataUpdate);
		return $update;
	}

	function delete($kd_layanan) {
		$this->db->where('kd_layanan', $kd_layanan);
		$delete = $this->db->delete('layanan');
		return $delete;
	}
}
